package com.nefu.softlab.xjwc.service.impl;

import com.nefu.softlab.xjwc.common.Constant;
import com.nefu.softlab.xjwc.mapper.ActivityMapper;
import com.nefu.softlab.xjwc.mapper.ProjectMapper;
import com.nefu.softlab.xjwc.mapper.RedisMapper;
import com.nefu.softlab.xjwc.mapper.ScoreMapper;
import com.nefu.softlab.xjwc.model.Activity;
import com.nefu.softlab.xjwc.model.Project;
import com.nefu.softlab.xjwc.model.Score;
import com.nefu.softlab.xjwc.model.vo.ScoreVo;
import com.nefu.softlab.xjwc.service.ExportExcelService;
import com.nefu.softlab.xjwc.utils.FileUtil;
import com.nefu.softlab.xjwc.utils.JsonUtil;
import com.nefu.softlab.xjwc.utils.XJWCException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;

/**
 * @author LiXiwen
 * @date 2020/4/15 15:07
 */
@Service
public class ExportExcelServiceImpl implements ExportExcelService {

    private final ScoreMapper scoreMapper;
    private final RedisMapper redisMapper;
    private final ProjectMapper projectMapper;
    private final ActivityMapper activityMapper;

    @Autowired
    public ExportExcelServiceImpl(ScoreMapper scoreMapper, RedisMapper redisMapper, ProjectMapper projectMapper, ActivityMapper activityMapper) {
        this.scoreMapper = scoreMapper;
        this.redisMapper = redisMapper;
        this.projectMapper = projectMapper;
        this.activityMapper = activityMapper;
    }


    @SuppressWarnings("Duplicates")
    @Override
    public String exportTotalProjectExcelByProjectId(Integer projectId, HttpServletResponse response, HttpServletRequest request) {
        Score s = new Score();
        s.setProjectId(projectId);

        String name = projectMapper.selectProjectName(projectId);

        // 获取人数，也就是行
        List<ScoreVo> scores = scoreMapper.selectScoreByProject(s);

        //判断当前人员是否能评分
        List<Activity> list0 = activityMapper.selectActivityByProjectId(projectId);
        if (list0.size() != 1) {
            return "失败1";
        }
        if (!redisMapper.hasKey(list0.get(0).getActivityId().toString())) {
            return "失败2";
        }
        List<Map<String, Object>> permitUsers = (List<Map<String, Object>>) redisMapper.get(list0.get(0).getActivityId().toString());
        List<String> permitUserId = new ArrayList<>();
        for (Map<String, Object> map : permitUsers) {
            permitUserId.add((String)map.get("userId"));
        }
        for (int i = 0; i < scores.size(); i++) {
            if (!permitUserId.contains(scores.get(i).getUserId())) {
                scores.remove(i);
            }
        }
        List<Project> getProjectScore = projectMapper.selectAllProject2(new Project(projectId));
        if (getProjectScore.size() != 1) {
            return "失败3";
        }

        String fin = getProjectScore.get(0).getScore();

        // 获取评分因素个数，也就是列
        List<HashMap<String, Object>> list1 = JsonUtil.getListFromJson(scores.get(0).getScore());

        int[][] a = new int[scores.size() + 1][list1.size() + 1];

        //最大值数组
        int[] maxScores = new int[list1.size()];
        Arrays.fill(maxScores, 0);

        //最小值数组
        int[] minScores = new int[list1.size()];
        Arrays.fill(minScores, 101);

        List<String> yinsu = new ArrayList<>();
        // 把因素名字存起来
        list1.parallelStream().forEach(map -> yinsu.add((String) map.get("lname")));

        List<String> userList = new ArrayList<>();

        // 获取每个因素最高分, 填充数组
        for (int i = 0; i < scores.size(); i++) {
            // 把专家名字存起来
            userList.add(scores.get(i).getUserName());
            List<LinkedHashMap<String, Object>> list = JsonUtil.getListFromJson(scores.get(i).getScore());
            for (int j = 0; j < list.size(); j++) {
                LinkedHashMap<String, Object> map = list.get(j);
                a[i][j] = (Integer) map.get("value");
                maxScores[j] = Math.max(maxScores[j], a[i][j]);
                minScores[j] = Math.min(minScores[j], a[i][j]);
            }
        }

        int maxScoreSum = 0;
        int minScoreSum = 1001;

        // 把二维数组填充好。包括总分。
        for (int i = 0; i < a.length - 1; i++) {
            //和
            a[i][a[0].length - 1] = Arrays.stream(Arrays.copyOfRange(a[i], 0, a.length - 1)).sum();

            maxScoreSum = Math.max(maxScoreSum, a[i][a[0].length - 1]);
            minScoreSum = Math.min(minScoreSum, a[i][a[0].length - 1]);
        }

        for (int j = 0; j < a[0].length - 1; j++) {
            a[a.length - 1][a[0].length - 1] += a[a.length - 1][j];
        }

        //1、获取文件输入流
        InputStream inputStream = null;
        FileOutputStream fos = null;
        //2、获取Excel工作簿对象
        HSSFWorkbook workbook = null;
        try {
            // inputStream = new FileInputStream( "C:\\Users\\92590\\Desktop\\表.xls");
            //inputStream = new FileInputStream( "C:\\Users\\92590\\Desktop\\评审打分汇总表样表1.xls");
            inputStream = new FileInputStream(Constant.UPLOAD_EXCEL_PATH + "评审打分汇总表样表.xls");
            workbook = new HSSFWorkbook(inputStream);
            //3、得到Excel工作表对象
            HSSFSheet sheetAt = workbook.getSheetAt(0);

            sheetAt.setDefaultRowHeight((short)600);

            // 设置列宽为 50
            for (int col = 0; col < yinsu.size() + 2; col++) {
                sheetAt.setColumnWidth(col, 256 * 18 + 144);
            }

            Row row0 = sheetAt.createRow(0);
            CellStyle style0 = workbook.createCellStyle();
            Font font0 = workbook.createFont();
            font0.setFontName("宋体");
            // 加粗
            font0.setBold(true);
            font0.setFontHeight((short)500);
            style0.setFont(font0);
            style0.setAlignment(HorizontalAlignment.CENTER);
            style0.setVerticalAlignment(VerticalAlignment.CENTER);
            row0.createCell(0).setCellValue(LocalDate.now().getYear() + "年" + name + "评分表");
            row0.getCell(0).setCellStyle(style0);

            // 第二行 项目 + tag
            CellStyle style1 = workbook.createCellStyle();
            Font font1 = workbook.createFont();
            font1.setFontName("宋体");
            font1.setFontHeight((short) 240);
            style1.setFont(font1);
            style1.setAlignment(HorizontalAlignment.CENTER);
            style1.setVerticalAlignment(VerticalAlignment.CENTER);
            Row row1 = sheetAt.createRow(1);
            row1.setHeight((short) 800);
            row1.createCell(0).setCellValue("序号");
            row1.getCell(0).setCellStyle(style1);
            row1.createCell(1).setCellValue(projectId.toString());
            row1.getCell(1).setCellStyle(style1);

            // 第三行，即是项目所在那一行，把因素名字换一下，从项目开始，到总分结束
            Row row = sheetAt.createRow(2);
            int excelCol = 1;
            int arrayCol = 0;
            row.createCell(0).setCellValue("因素");
            while (arrayCol < yinsu.size()) {
                row.createCell(excelCol++).setCellValue(yinsu.get(arrayCol++));
            }
            row.createCell(excelCol).setCellValue("总分");

            System.out.println("end--因素名字换一下");

            // 把专家名字替换下
            int excelRow = 3;
            int arrayRow = 0;
            //创建专家名字 行
            while (arrayRow < userList.size()) {
                sheetAt.createRow(excelRow++).createCell(0).setCellValue(userList.get(arrayRow++));
            }

            Row row3 = sheetAt.createRow(excelRow++);
            row3.createCell(0).setCellValue("项目总分");


            Row row4 = sheetAt.createRow(excelRow++);
            row4.setHeight((short) (1600));
            row4.createCell(0).setCellValue("评审意见和建议");


            // 设置专家签字的行高为 70
            Row row5 = sheetAt.createRow(excelRow);
            row5.setHeight((short) (1600));
            row5.createCell(0).setCellValue("专家签字");

            System.out.println("end--专家名字替换下");

            // 一行行把二维数组值填到表格
            for (int i = 0; i < a.length; i++) {
                int r = i + 3;
                row = sheetAt.getRow(r);
                for (int k = 0; k < a[i].length; k++) {
                    //maxScoreSum = Math.max(maxScoreSum, )
                    row.createCell(k + 1).setCellValue(a[i][k]);
                }
            }

            //填写项目总分
            row3.createCell(1).setCellValue(fin);

            //全局设定样式
            //项目人员样式1
            for (int i = 2; i < userList.size() + 4; i++) {
                sheetAt.getRow(i).setHeight((short)600);
                for (int j = 0; j < yinsu.size() + 2; j++) {
                    sheetAt.getRow(i).getCell(j).setCellStyle(style1);
                }
            }
            //意见签字样式2
            sheetAt.getRow(userList.size() + 4).getCell(0).setCellStyle(style1);
            sheetAt.getRow(userList.size() + 5).getCell(0).setCellStyle(style1);

            //合并单元格
            sheetAt.addMergedRegion(new CellRangeAddress(0, 0, 0, yinsu.size() + 1));
            sheetAt.addMergedRegion(new CellRangeAddress(1, 1, 1, yinsu.size() + 1));
            sheetAt.addMergedRegion(new CellRangeAddress(excelRow - 2, excelRow - 2, 1, yinsu.size() + 1));
            sheetAt.addMergedRegion(new CellRangeAddress(excelRow - 1, excelRow - 1, 1, yinsu.size() + 1));
            sheetAt.addMergedRegion(new CellRangeAddress(excelRow, excelRow, 1, yinsu.size() + 1));

            System.out.println("end--二维数组值填到表格");

            //最大值最小值填充
            if (list0.get(0).getType() == 3) {
                for (int j = 1; j < yinsu.size() + 1; j++) {
                    boolean flagMax = false;
                    boolean flagMin = false;
                    for (int i = 3; i < userList.size() + 3; i++) {
                        double value = sheetAt.getRow(i).getCell(j).getNumericCellValue();

                        if (!flagMax && value == maxScores[j - 1]) {
                            sheetAt.getRow(i).getCell(j).setCellValue((int)value + "*");
                            flagMax = true;
                        }
                        if (!flagMin && value == minScores[j - 1]) {
                            sheetAt.getRow(i).getCell(j).setCellValue((int)value + "*");
                            flagMin = true;
                        }
                        if(flagMax && flagMin) {
                            break;
                        }

                    }
                }
            } else if (list0.get(0).getType() == 4) {
                boolean flagMax = false;
                boolean flagMin = false;
                for (int i = 3; i < userList.size() + 3; i++) {
                    int last = yinsu.size() + 1;
                    double value = sheetAt.getRow(i).getCell(last).getNumericCellValue();
                    if (!flagMax && value == maxScoreSum) {
                        sheetAt.getRow(i).getCell(1 + yinsu.size()).setCellValue((int)value + "*");
                        flagMax = true;
                    }
                    if (!flagMin && value == minScoreSum) {
                        sheetAt.getRow(i).getCell(1 + yinsu.size()).setCellValue((int)value + "*");
                        flagMin = true;
                    }
                    if(flagMax && flagMin) {
                        break;
                    }
                }
            }

            System.out.println("end--z最大值最小值填充");

            String fileName = projectId + "." + name + "-评审打分汇总表.xls";
            // String path = "C:\\Users\\HP\\Desktop\\教务处评分系统\\" + fileName;
            //String path = "C:\\Users\\92590\\Desktop\\" + fileName;
            String path = Constant.UPLOAD_EXCEL_PATH + fileName;
            // 把更改的写回去, 需要保存一下位置，方便在查询的时候返回
            fos = new FileOutputStream(path);
            workbook.write(fos);

            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            boolean f = agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0);
            if (f) {
                //微软的浏览器(IE和Edge浏览器)
                try {
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
            response.setHeader("Content-Disposition", "filename=" + fileName);

            response.setContentType("application/octet-stream");

            try {
                FileInputStream fis = new FileInputStream(path);
                byte[] content = new byte[fis.available()];
                fis.read(content);
                ServletOutputStream sos = response.getOutputStream();
                sos.write(content);
                sos.flush();
                sos.close();
                fis.close();
            } catch (Exception e) {
                return "下载失败";
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5、关闭流
            try {
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return "成功";
    }

    @SuppressWarnings("Duplicates")
    @Override
    public String downloadTotalProjectExcelByProjectId(Integer projectId, HttpServletResponse response, HttpServletRequest request)  throws XJWCException {
        List<File> files = FileUtil.getAllFile(Constant.UPLOAD_EXCEL_PATH);
        boolean f1 = false;

        for (File file : files) {
            String fileName = file.getName();
            if (fileName.contains(String.valueOf(projectId))) {
                String path = Constant.UPLOAD_EXCEL_PATH + fileName;
                f1 = true;
                //获得浏览器信息并转换为大写
                String agent = request.getHeader("User-Agent").toUpperCase();
                boolean f = agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0);
                if (f) {
                    //微软的浏览器(IE和Edge浏览器)
                    try {
                        fileName = URLEncoder.encode(fileName, "UTF-8");
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    }
                } else {
                    try {
                        fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    }
                }
                response.setHeader("Content-Disposition", "filename=" + fileName);

                response.setContentType("application/octet-stream");

                try {
                    FileInputStream fis = new FileInputStream(path);
                    byte[] content = new byte[fis.available()];
                    fis.read(content);
                    ServletOutputStream sos = response.getOutputStream();
                    sos.write(content);
                    sos.flush();
                    sos.close();
                    fis.close();
                } catch (Exception e) {
                    return "下载失败";
                }
            }
        }
        if (!f1) {
            throw new XJWCException("未查询到此文件!");
        }
        return null;
    }

    @Override
    public String uploadExcelModel(MultipartFile[] files) {
        Future<Boolean> future = FileUtil.uploadFileAsync(files[0], Constant.UPLOAD_EXCEL_PATH);
        try {
            if (future.get()) {
                return "上传成功!";
            } else {
                return "上传失败!";
            }
        } catch (InterruptedException e) {
            e.printStackTrace();
            return "上传失败!";
        } catch (ExecutionException e) {
            e.printStackTrace();
            return "上传失败!";
        }
    }

    @Override
    public List<String> getExcelModels() {
        List<File> files = FileUtil.getAllFile(Constant.UPLOAD_EXCEL_PATH);
        List<String> res = new ArrayList<>();
        files.parallelStream().forEach(file -> res.add(file.getName()));
        return res;
    }

    @SuppressWarnings("Duplicates")
    @Override
    public String exportExcelByUserIdAndProId(Integer projectId, String userId, HttpServletResponse response, HttpServletRequest request) {
        Score s = new Score();
        s.setProjectId(projectId);
        s.setUserId(userId);

        String projectName = projectMapper.selectProjectName(projectId);

        List<ScoreVo> scoreVos = scoreMapper.selectSingleScoreByUserIdAndProId(s);
        if (scoreVos == null || scoreVos.size() < 1) {
            return "请评完分再下载!";
        }

        ScoreVo scoreVo = scoreVos.get(0);

        // 获取这个评委的评分
        List<LinkedHashMap<String, Object>> list1 = JsonUtil.getListFromJson(scoreVo.getScore());

        // 存这个评委的每项打分和总分
        int[] a = new int[list1.size() + 1];
        int i = 0;

        List<String> yinsu = new ArrayList<>();
        // 把因素名字存起来
        for (LinkedHashMap<String, Object> map : list1) {
            yinsu.add((String) map.get("lname"));
            a[i] = (int) map.get("value");
            a[list1.size()] += a[i];
            i++;
        }

        //1、获取文件输入流
        InputStream inputStream = null;
        FileOutputStream fos = null;
        //2、获取Excel工作簿对象
        HSSFWorkbook workbook = null;
        try {
            // inputStream = new FileInputStream( "C:\\Users\\HP\\Desktop\\教务处评分系统\\评审意见表模板.xls");

            inputStream = new FileInputStream(Constant.UPLOAD_EXCEL_PATH + "评审意见表模板.xls");
            workbook = new HSSFWorkbook(inputStream);
            //3、得到Excel工作表对象
            HSSFSheet sheetAt = workbook.getSheetAt(0);

            // 设置列宽为 50
            for (int col = 0; col < yinsu.size() + 2; col++) {
                sheetAt.setColumnWidth(col, 256 * 20 + 144);
            }

            Row row0 = sheetAt.createRow(0);
            row0.setHeight((short) 900);
            CellStyle style0 = workbook.createCellStyle();
            Font font0 = workbook.createFont();
            font0.setFontName("宋体");
            font0.setBold(true); // 加粗
            font0.setFontHeight((short) 500);
            style0.setFont(font0);
            style0.setAlignment(HorizontalAlignment.CENTER);
            style0.setVerticalAlignment(VerticalAlignment.CENTER);
            row0.createCell(0).setCellValue(LocalDate.now().getYear() + "年" + projectName + "专家评审意见表");
            row0.getCell(0).setCellStyle(style0);

            // 第二行 评分因素
            CellStyle style1 = workbook.createCellStyle();
            Font font1 = workbook.createFont();
            font1.setFontName("宋体");
            font1.setFontHeight((short) 240);
            style1.setFont(font1);
            style1.setAlignment(HorizontalAlignment.CENTER);
            style1.setVerticalAlignment(VerticalAlignment.CENTER);

            Row row1 = sheetAt.createRow(1);
            row1.setHeight((short) 800);
            row1.createCell(0).setCellValue("项目");
            row1.getCell(0).setCellStyle(style1);

            int excelCol = 1;
            int arrayCol = 0;
            while (arrayCol < yinsu.size()) {
                row1.createCell(excelCol).setCellValue(yinsu.get(arrayCol++));
                row1.getCell(excelCol).setCellStyle(style1);
                excelCol++;
            }
            row1.createCell(excelCol).setCellValue("总分");
            row1.getCell(excelCol).setCellStyle(style1);

            // 第三行，分数
            Row row2 = sheetAt.createRow(2);
            row2.setHeight((short) 800);
            row2.createCell(0).setCellValue("分数");
            row2.getCell(0).setCellStyle(style1);

            excelCol = 1;
            arrayCol = 0;
            while (arrayCol < a.length) {
                row2.createCell(excelCol).setCellValue(a[arrayCol++]);
                row2.getCell(excelCol).setCellStyle(style1);
                excelCol++;
            }

            // 第4行，评审意见和建议
            Row row3 = sheetAt.createRow(3);
            row3.setHeight((short) 1600);
            row3.createCell(0).setCellValue("评审意见和建议");
            row3.getCell(0).setCellStyle(style1);
            row3.createCell(1).setCellValue(scoreVo.getMessage());
            row3.getCell(1).setCellStyle(style1);

            sheetAt.addMergedRegion(new CellRangeAddress(3, 3, 1, yinsu.size() + 1));


            // 第5行，设置专家签字的行高为 70
            Row row4 = sheetAt.createRow(4);
            row4.setHeight((short) (1600));
            row4.createCell(0).setCellValue("专家签字");
            row4.getCell(0).setCellStyle(style1);

            sheetAt.addMergedRegion(new CellRangeAddress(0, 0, 0, yinsu.size() + 1));
            sheetAt.addMergedRegion(new CellRangeAddress(4, 4, 1, yinsu.size() + 1));

            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, yinsu.size() + 1);
            setBorder(cellRangeAddress, sheetAt);

            for (int k = 1; k <= 2; k++) {
                for (int n = 1; n <= yinsu.size() + 1; n++) {
                    CellRangeAddress cellRangeAddress1 = new CellRangeAddress(k, k, n - 1, n);
                    setBorder(cellRangeAddress1, sheetAt);
                }
            }

            for (int k = 3; k <= 4; k++) {
                CellRangeAddress cellRangeAddress1 = new CellRangeAddress(k, k, 0, 1);
                setBorder(cellRangeAddress1, sheetAt);
                cellRangeAddress1 = new CellRangeAddress(k, k, 1, yinsu.size() + 1);
                setBorder(cellRangeAddress1, sheetAt);
            }

            String fileName = projectId + "-" + projectName + "-" + scoreVo.getUserName() + "专家评审意见表.xls";
            // String path = "C:\\Users\\HP\\Desktop\\教务处评分系统\\" + fileName;

            String path = Constant.UPLOAD_EXCEL_PATH + fileName;

            // 把更改的写回去, 需要保存一下位置，方便在查询的时候返回
            fos = new FileOutputStream(path);
            workbook.write(fos);

            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            boolean f = agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0);
            if (f) {
                //微软的浏览器(IE和Edge浏览器)
                try {
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
            response.setHeader("Content-Disposition", "filename=" + fileName);

            response.setContentType("application/octet-stream");

            try {
                FileInputStream fis = new FileInputStream(path);
                byte[] content = new byte[fis.available()];
                fis.read(content);
                ServletOutputStream sos = response.getOutputStream();
                sos.write(content);
                sos.flush();
                sos.close();
                fis.close();
            } catch (Exception e) {
                return "下载失败";
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5、关闭流
            try {
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return "下载成功";
    }

    @SuppressWarnings("Duplicates")
    @Override
    public String exportExcelByActivityId(Integer activityId, HttpServletResponse response, HttpServletRequest request) throws XJWCException {
        List<Project> projects = projectMapper.selectAllProjectByActivityIdNoContainsFile(activityId);
        Activity activity = new Activity();
        activity.setActivityId(activityId);
        activity = activityMapper.selectAllActivity(activity).get(0);

        for (Project p : projects) {
            if ("0".equals(p.getScore())) {
                throw new XJWCException("该活动下还有项目为评分，请评完分后下载!");
            }
        }

        FileOutputStream fos = null;
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("汇总表");
        try {
            // 设置列宽为
            sheet.setColumnWidth(0, 256 * 20 + 144);
            sheet.setColumnWidth(1, 256 * 40 + 144);
            sheet.setColumnWidth(2, 256 * 20 + 144);

            Row row0 = sheet.createRow(0);
            row0.setHeight((short) 1200);
            CellStyle style0 = wb.createCellStyle();
            Font font0 = wb.createFont();
            font0.setFontName("宋体");
            font0.setBold(true); // 加粗
            font0.setFontHeight((short) 500);
            style0.setFont(font0);
            style0.setAlignment(HorizontalAlignment.CENTER);
            style0.setVerticalAlignment(VerticalAlignment.CENTER);
            //创建HSSFCell对象
            Cell cell = row0.createCell(0);
            //设置单元格的值
            cell.setCellValue(LocalDate.now().getYear() + "年" + activity.getName() + "评审汇总表");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
            row0.getCell(0).setCellStyle(style0);

            // 第二行，列名称
            CellStyle style1 = wb.createCellStyle();
            Font font1 = wb.createFont();
            font1.setFontName("宋体");
            font1.setFontHeight((short) 240);
            style1.setFont(font1);
            style1.setAlignment(HorizontalAlignment.CENTER);
            style1.setVerticalAlignment(VerticalAlignment.CENTER);

            Row row1 = sheet.createRow(1);
            row1.setHeight((short) 500);
            row1.createCell(0).setCellValue("序号");
            row1.getCell(0).setCellStyle(style1);
            row1.createCell(1).setCellValue("项目");
            row1.getCell(1).setCellStyle(style1);
            row1.createCell(2).setCellValue("总分");
            row1.getCell(2).setCellStyle(style1);

            int rowI = 2;
            for (int i = 0; i < projects.size(); i++) {
                Row row = sheet.createRow(rowI++);
                row.setHeight((short) 500);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(projects.get(i).getName());
                row.createCell(2).setCellValue(projects.get(i).getScore());
                for (int j = 0; j < 3; j++) {
                    row.getCell(j).setCellStyle(style1);
                }
            }

            // 专家签字
            Row rowLast = sheet.createRow(rowI);
            rowLast.setHeight((short) 1200);
            Cell cellLast = rowLast.createCell(0);
            //设置单元格的值
            cellLast.setCellValue("专家签字");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(rowI,rowI,1,2));
            rowLast.getCell(0).setCellStyle(style1);

            String fileName = activityId + "-" + activity.getName() + "评审汇总表.xls";

            String path = Constant.UPLOAD_EXCEL_PATH + fileName;
            //String path = "G:\\" + fileName;
            // 把更改的写回去, 需要保存一下位置，方便在查询的时候返回
            fos = new FileOutputStream(path);
            wb.write(fos);

            //获得浏览器信息并转换为大写
            String agent = request.getHeader("User-Agent").toUpperCase();
            boolean f = agent.indexOf("MSIE") > 0 || (agent.indexOf("GECKO") > 0 && agent.indexOf("RV:11") > 0);
            if (f) {
                //微软的浏览器(IE和Edge浏览器)
                try {
                    fileName = URLEncoder.encode(fileName, "UTF-8");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
            response.setHeader("Content-Disposition", "filename=" + fileName);

            response.setContentType("application/octet-stream");

            try {
                FileInputStream fis = new FileInputStream(path);
                byte[] content = new byte[fis.available()];
                fis.read(content);
                ServletOutputStream sos = response.getOutputStream();
                sos.write(content);
                sos.flush();
                sos.close();
                fis.close();
            } catch (Exception e) {
                return "下载失败";
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5、关闭流
            try {
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return "成功";
    }

    public void setBorder(CellRangeAddress cellRangeAddress, Sheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
    }


}